﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace BingoLib
{
    public class SSQ
    {
        public class Issue
        {
            public static DataTable getPid(int max)
            {
                string sql = "select" + (max == 0 ? "" : (" top " + max)) + " [pid] from [QT_SSQ_Issue] order by [pid] desc";
                DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
                return dt;
            }

            public static DataTable get(string pid)
            {
                string sql = "select [pid],[RunDate],[Red],[Blue] from [QT_SSQ_Issue] where [pid]='" + pid + "' order by [pid] desc";
                DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
                return dt;
            }

            public static DataTable getAll()
            {
                string sql = "select [pid],[RunDate],[Red],[Blue] from [QT_SSQ_Issue] order by [pid] desc";
                DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
                return dt;
            }

            public static DataTable getTop(int max)
            {
                string sql = "select top " + max + " [pid],[RunDate],[Red],[Blue] from [QT_SSQ_Issue] order by [pid] desc";
                DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
                return dt;
            }

            public static int Add(StructSSQ lo, bool b)
            {
                if (IsExist(lo.pid))
                {
                    if (b) return Update(lo);
                    else return -2;
                }

                int rval=-1;
                string sql = "insert into [QT_SSQ_Issue]([pid],[rundate],[red],[blue]) values('" + lo.pid + "','" + lo.rundate + "','" + lo.red + "','" + lo.blue + "')";
                rval = new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
                if (rval <= 0) return rval;

                return Number.Add(lo);
            }

            public static int Update(StructSSQ lo)
            {
                int rval = -1;

                string sql = "update [QT_SSQ_Issue] set [rundate]='" + lo.rundate + "',[red]='" + lo.red + "',[blue]='" + lo.blue + "' where [pid]='" + lo.pid + "'";
                rval= new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
                if (rval <= 0) return rval;

                return Number.Add(lo);
            }

            public static int Delete(List<string> pids)
            {
                if (Number.Delete(pids) == -1) return -1;

                string sql = "delete from [QT_SSQ_Issue] where [pid] in('" + string.Join("','", pids.ToArray()) + "')";
                return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
            }

            public static bool IsExist(string pid)
            {
                bool b = false;

                string sql = "select [pid] from [QT_SSQ_Issue] where [pid]='" + pid + "'";
                DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
                if (dt.Rows.Count > 0) b = true;

                return b;
            }
        }

        public class Number
        {
            public static int Add(StructSSQ lo)
            {
                int rval = 0;
                string sql = "";

                if (Delete(new List<string>(new string[] { lo.pid })) == -1) return -1;

                List<string> nums = new List<string>(lo.red.Split(';'));
                foreach (string num in nums)
                {
                    sql = "insert into [QT_SSQ_Red]([pid],[rnum]) values('" + lo.pid + "'," + num + ")";
                    if (new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql) == 1) rval = 1;
                }
                sql = "insert into [QT_SSQ_Blue]([pid],[bnum]) values('" + lo.pid + "'," + lo.blue + ")";
                if (new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql) < 0) return -1;

                return rval;
            }

            public static int Delete(List<string> pids)
            {
                string sql = "";

                sql = "delete from [QT_SSQ_Red] where [pid] in('" + string.Join("','",pids.ToArray()) + "')";
                if (new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql) < 0) return -1;
                sql = "delete from [QT_SSQ_Blue] where [pid] in('" + string.Join("','",pids.ToArray()) + "')";
                if (new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql) < 0) return -1;

                return 0;
            }

            public static DataTable getRedCount(string sTime, string eTime)
            {
                string sql = "select [RNum],count(*) from [QT_SSQ_Red] " + ((sTime != null && eTime != null) ? ("where [pid] in(select [pid] from [QT_SSQ_Issue] where CDate([RunDate])>=#" + sTime + "# and CDate([RunDate])<=#" + eTime + "#) ") : "") + "group by [RNum] order by [RNum] asc";
                DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);

                return dt;
            }

            public static DataTable getRedCount(string sort,string sTime,string eTime)
            {
                string sql = "select [RNum],count(*) from [QT_SSQ_Red] " + ((sTime != null && eTime != null) ? ("where [pid] in(select [pid] from [QT_SSQ_Issue] where CDate([RunDate])>=#" + sTime + "# and CDate([RunDate])<=#" + eTime + "#) ") : "") + "group by [RNum] order by count(*) " + sort;
                DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);

                return dt;
            }

            public static DataTable getBlueCount(string sTime, string eTime)
            {
                string sql = "select [BNum],count(*) from [QT_SSQ_Blue] " + ((sTime != null && eTime != null) ? ("where [pid] in(select [pid] from [QT_SSQ_Issue] where CDate([RunDate])>=#" + sTime + "# and CDate([RunDate])<=#" + eTime + "#) ") : "") + "group by [BNum] order by [BNum] asc";
                DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);

                return dt;
            }

            public static DataTable getBlueCount(string sort, string sTime, string eTime)
            {
                string sql = "select [BNum],count(*) from [QT_SSQ_Blue] " + ((sTime != null && eTime != null) ? ("where [pid] in(select [pid] from [QT_SSQ_Issue] where CDate([RunDate])>=#" + sTime + "# and CDate([RunDate])<=#" + eTime + "#) ") : "") + "group by [BNum] order by count(*) " + sort;
                DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);

                return dt;
            }
        }

        public class Setting
        {
            public static string get(string attrib)
            {
                string sql = "select [content] from [QT_SSQ_Setting] where [Attrib]='" + attrib + "'";
                DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
                if (dt.Rows.Count > 0) return dt.Rows[0][0].ToString();
                else return "";
            }

            public static int update(string attrib, string content)
            {
                string sql = "update [QT_SSQ_Setting] set [content]='" + content + "' where [Attrib]='" + attrib + "'";
                return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
            }

            public static DataTable getResult(string type)
            {
                string sql = "select [gid],[result] from [QT_Result] where [type]='" + type + "'";
                DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
                return dt;
            }
        }
    }

    public struct StructSSQ
    {
        public string pid;
        public string rundate;
        public string red;
        public string blue;
    }
}
